package com.mycompany.inventory.persistence;

import com.mycompany.common.AbstractDao;
import com.mycompany.common.jdbc.ConnectionDatabase;
import com.mycompany.inventory.pojo.CustomInfo;

import java.util.ArrayList;
import java.util.List;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by My Company.
 * User: loitran
 * Date: Feb 29, 2012
 * Time: 1:53:19 PM
 * com.mycompany.inventory.persistence.
 */
public class CustomFieldDAO extends AbstractDao<CustomInfo>{
	
	private Connection con = this.connect;
	public static final String SELECT_ALL_SQL = "select * from oce_custom_field";
	public static final String SELECT_ALL_VENDOR_SQL = "select * from oce_custom_field where type = 'Vendor' order by customId";
	public static final String SELECT_ALL_CUSTOMER_SQL = "select * from oce_custom_field where type = 'Customer' order by customId";
	public static final String SELECT_ALL_PRODUCT_SQL = "select * from oce_custom_field where type = 'Product' order by customId";	
	public static final String SELECT_SQL = "select * from oce_custom_field where customId = ?";
	public static final String INSERT_SQL = "INSERT INTO oce_custom_field (customId,name,show,type) VALUES (?,?,?,?)";
	public static final String UPDATE_SQL = "UPDATE oce_custom_field SET name = ? , showed = ? ,type = ? WHERE customId = ?";
	
	public CustomInfo create(CustomInfo obj){
		try{
			PreparedStatement ps = con.prepareStatement(INSERT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);	
			long id = 0l;
			ps.setLong(1,id);
			ps.setString(2, obj.getName());
			ps.setInt(3, obj.isShowed() == true ? 1 : 0);
			ps.setString(4, obj.getName());
			
			ConnectionDatabase.executeUpdate(ps);	
			ResultSet rs=ps.getGeneratedKeys();
            if(rs.next()){
                Integer Id = (Integer) rs.getInt(1);               
                obj.setId(String.valueOf(Id));
            }
			ps.close();			
			
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}
	
	public CustomInfo update(CustomInfo obj){
		try{
			PreparedStatement ps = con.prepareStatement(UPDATE_SQL, PreparedStatement.RETURN_GENERATED_KEYS);				
			
			ps.setString(1, obj.getName());			
			if(obj.isShowed()){
				ps.setLong(2,  1l);
			}else{
				ps.setLong(2,  0l);
			}
			ps.setString(3, obj.getType());
			ps.setLong(4,Long.parseLong(obj.getId()));
			
			ConnectionDatabase.executeUpdate(ps);	
			
			ps.close();	
		}catch (Exception e) {
			// TODO: handle exception
			obj = null;
		}
		return obj;
	}
	
	public CustomInfo find(long id){
		List<CustomInfo> customFields = new ArrayList<CustomInfo>();
		try{
			PreparedStatement ps = con.prepareStatement(SELECT_SQL, PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setLong(1,id);
			customFields = convert(ConnectionDatabase.executeQuery(ps));
		}catch (Exception e) {
			// TODO: handle exception
		}
		return customFields.size()== 0 ? null : customFields.get(0);
	}
	
	public List<CustomInfo> findAll(){
		List<CustomInfo> customFields = new ArrayList<CustomInfo>();
		try{
			customFields = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
		}catch (Exception e) {
			// TODO: handle exception
			customFields = null;
		}
		return customFields;
	}
	
	public List<CustomInfo> findAll(String type){
		List<CustomInfo> customFields = new ArrayList<CustomInfo>();
		try{
			if(type.equals("Vendor")){
				customFields = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_VENDOR_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
			}else if (type.equals("Customer")){
				customFields = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_CUSTOMER_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
			}else{
				customFields = convert(ConnectionDatabase.executeQuery(con.prepareStatement(SELECT_ALL_PRODUCT_SQL, PreparedStatement.RETURN_GENERATED_KEYS)));
			}
		}catch (Exception e) {
			// TODO: handle exception
			customFields = null;
		}
		return customFields;
	}
	
	public void delete(CustomInfo obj){
		try{
			
		}catch (Exception e) {
			// TODO: handle exception
		}
	}
	
	private List<CustomInfo> convert(ResultSet rs) throws SQLException {
		List<CustomInfo> customFields = new ArrayList<CustomInfo>();
		while (rs.next()) {
	        // Get the data from the row using the column index
	        String id = rs.getString(1);
	        String name = rs.getString(2);
	        String showed = rs.getString(3);
	        String type = rs.getString(4);
	        	        
	        customFields.add(new CustomInfo(name,"", id, type, showed.equals("1") ? true : false ));
	    }
		rs.close();
		return customFields;
	}
}
